﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.IncomeBook
{
    public class DataLoader
    {
        public static void GetData(Reports.IncomeBook.DataSet.DataDataTable data, string ConnectionString, string depName, DateTime dateF,DateTime dateT)
        {
            string QueryString =
@"SELECT distinct @DepartName as DepartName,--    CA.Documents.Id, 
CA.Contragents.ShortName as SupplierName,
CA.Documents.ContragentDocDate as DocDate,
--CA.Documents.DocDate,
CA.Documents.ContragentDocNumber as SupplierDocNum,
(SELECT     SUM(CD.InputLines.SupplierPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)) AS Sum,
(SELECT     SUM(CD.InputLines.NDSValue) 
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id))AS VatSum,
(SELECT     SUM(CD.Parties.RetailPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)) AS AptSum,
(SELECT sum(CD.InputLines.DocQntty * CD.Parties.RetailPrice * (1-(1/(1+ISNULL(CD.InputLines.NDSTax, 0) / cast(100 as decimal)))))   
FROM CD.InputLines INNER JOIN
CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE CD.Parties.DocId = CA.Documents.Id
) as AptVatSum,
CAST(0 as decimal) as Pack,
CAST(0 as decimal) as SpareMaterials
FROM CA.Documents INNER JOIN
CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id
INNER JOIN CD.Parties ON
CD.Parties.DocId = CA.Documents.Id
WHERE     (CA.Documents.DocTypeId = 2) and CA.Documents.DocDate BETWEEN @DateF and @DateT";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DepartName = new SqlParameter("@DepartName", SqlDbType.VarChar, 30);
                    DepartName.Value = depName;
                    command.Parameters.Add(DepartName);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
